from typing import List

from app.services.business.gaolu_diagnose.ind.abstract_ind import AbstractInd


class CG_LT_GL_GL04_Zha_R(AbstractInd):

    def get_name(self):
        return "CG_LT_GL_GL04_Zha_R"

    def get_data_source(self):
        return "mysql"

    def get_sql(self, params) -> list:
        sql_Zha_CaO = """   
        select round(avg(jyh.jyjg),3) as CG_LT_GL_GL04_Zha_CaO 
        from 
        (select jyh.*,jyt.ph, tqpd.tjsj
        from syn_zl_zl_jyh as jyh ,syn_zl_zl_jyt as jyt ,syn_zl_zl_tqpd as tqpd
        where  jyh.jcxmbm='01010' and jyh.zfbz='0' 
        and jyt.wlbm = 'ZJ003' 
        and SUBSTRING(jyt.ph, 4, 1) = '4' 
        and  jyt.jytid = jyh.jytid 
        and  jyt.ph = tqpd.ph
        and jyh.jyjg NOT LIKE '%#%' 
        and tqpd.tjsj > DATE_SUB(CURTIME(), INTERVAL 0.6 hour) ) as jyh 


                """.format(**params)

        sql_Zha_SiO2 = """     
        select round(avg(jyh.jyjg),3) as CG_LT_GL_GL04_Zha_SiO2 
        from 
        (select jyh.*,jyt.ph, tqpd.tjsj
        from syn_zl_zl_jyh as jyh ,syn_zl_zl_jyt as jyt ,syn_zl_zl_tqpd as tqpd
        where  jyh.jcxmbm='01011' and jyh.zfbz='0' 
        and jyt.wlbm = 'ZJ003' 
        and SUBSTRING(jyt.ph, 4, 1) = '4' 
        and  jyt.jytid = jyh.jytid 
        and  jyt.ph = tqpd.ph
        and jyh.jyjg NOT LIKE '%#%' 
        and tqpd.tjsj > DATE_SUB(CURTIME(), INTERVAL 0.6 hour)  ) as jyh
                """.format(**params)

        return [sql_Zha_CaO, sql_Zha_SiO2]

    @AbstractInd.cache_wrapper
    def get_result(self):
        sql_list: List[str] = self.get_sql(self.params)
        cursor = self.mysql_cursor

        cao = sql_list[0]
        cursor.execute(cao)
        cao = cursor.fetchone()

        cao = cao['CG_LT_GL_GL04_Zha_CaO'] if cao['CG_LT_GL_GL04_Zha_CaO'] is not None else 0

        sio2 = sql_list[1]
        cursor.execute(sio2)
        sio2 = cursor.fetchone()
        sio2 = sio2['CG_LT_GL_GL04_Zha_SiO2'] if sio2['CG_LT_GL_GL04_Zha_SiO2'] is not None else 0

        if cao == 0 or sio2 == 0: return self.get_cache()

        v = round(cao / sio2, 2)
        return v
